# -*- codeing = utf-8 -*-
# @Time: 2021/8/13 12:19
# @Author: Foxhuty
# @File: score_for_senior_one.py
# @Software: PyCharm
# @Based on python 3.9
import time
import pandas as pd
import numpy as np
import os
import random


class SeniorOneExam(object):
    good_scores = []
    top_n = None
    room_num = None

    def __init__(self, filepath):
        self.filepath = filepath
        self.df = pd.read_excel(filepath, sheet_name='总表',
                                dtype={
                                    '班级': str,
                                    '序号': str,
                                    '名次': str,
                                    '考生号': str,
                                    '考号': str
                                }
                                )

    def __str__(self):
        return f'正在对{os.path.basename(self.filepath)}进行成绩分析处理。'

    @staticmethod
    def make_directory(f):
        def wrapper(*args, **kwargs):
            if not os.path.exists('D:\\成绩统计结果'):
                os.makedirs('D:\\成绩统计结果')
            result = f(*args, **kwargs)
            return result

        return wrapper

    @staticmethod
    def use_time(f):
        def wrapper(*args, **kwargs):
            t1 = time.time()
            results = f(*args, **kwargs)
            t2 = time.time()
            print(f'{f.__name__}用时{(t2 - t1):.1f}秒')
            return results

        return wrapper

    @staticmethod
    def title_lines(f):
        def wrapper(*args, **kwargs):
            print(f'+++++++++++++++高中考试成绩分析处理+++++++++++++++')
            f(*args, **kwargs)
            print(f'--------------成绩分析处理完毕，程序已关闭-----------------')

        return wrapper

    @staticmethod
    def get_top_n(df_data, subject, n=None):
        df = df_data.sort_values(by=subject, ascending=False).reset_index()
        top_students = df.loc[0:n - 1, ['班级', '姓名', subject]]
        top_list = []
        while df[subject].at[n] == df[subject].at[n - 1]:
            # top_students = top_students.append(df.loc[n, ['班级', '姓名', subject]])
            # 新版pandas取消了append方法，代码修改如下：
            top_list.append(df.loc[n, ['班级', '姓名', subject]])
            n += 1
        top_list_df = pd.DataFrame(top_list, columns=top_students.columns)
        top_students = pd.concat([top_students, top_list_df])
        return top_students

    def exam_room(self):
        """
        计算生成各考室学生名单
        :return:
        """
        df = self.df.copy()
        df.sort_values(by='总分', ascending=False, inplace=True)
        if len(df) % SeniorOneExam.room_num != 0:
            room_numbers = [f'第{str(i + 1)}考室' for i in list(range(len(df) // SeniorOneExam.room_num + 1))]
        else:
            room_numbers = [f'第{str(i + 1)}考室' for i in list(range(len(df) // SeniorOneExam.room_num))]
        df['考室号'] = None
        df['座位号'] = None
        df = df.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
        df.reset_index(drop=True, inplace=True)
        print(room_numbers)
        df_room_students = []
        df_temp = pd.DataFrame(columns=df.columns)
        for idx, room_number in enumerate(room_numbers):
            begin = idx * SeniorOneExam.room_num
            end = begin + SeniorOneExam.room_num
            df_room_student = df.iloc[begin:end]
            df_room_students.append((idx, room_number, df_room_student))

        writer = pd.ExcelWriter(r'D:\成绩统计结果\高一(初中)考室安排表.xlsx')

        for idx, room_number, df_room_student in df_room_students:
            for i in df_room_student.index:
                df_room_student = df_room_student.copy()
                df_room_student['考室号'].at[i] = room_number
                df_room_student['座位号'].at[
                    i] = i + 1 if i < SeniorOneExam.room_num else i - idx * SeniorOneExam.room_num + 1
            df_room_student.to_excel(writer, sheet_name=room_number, index=False)
            # df_temp = df_temp.append(df_room_student)
            # 新版pandas取消了append方法。用concat修改代码如下:
            df_temp = pd.concat([df_temp, df_room_student])
        df_temp.to_excel(writer, sheet_name='考室安排表', index=False)

        for i in df_temp.index:
            df_temp.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
        seat_label = df_temp.sort_index()
        seat_label.to_excel(writer, sheet_name='考生座签', index=False)
        writer.close()

    def get_av(self):
        av_class = self.df.groupby(['班级'])[
            ['语文', '数学', '英语', '政治', '历史', '地理', '物理', '化学', '生物', '总分']].mean()
        av_general = self.df[['语文', '数学', '英语', '政治', '历史', '地理', '物理', '化学', '生物', '总分']].apply(
            np.nanmean, axis=0)
        av_class.loc['年级平均分'] = av_general
        av_scores = av_class
        # av_scores = av_class.join(av_general)
        # av_scores = pd.concat((av_class, av_general),axis=1)
        return av_scores

    def get_good_scores(self, goodtotal=None):
        """
                计算文科各科有效分
                goodtotal:划线总分，高线，中线，低线
                """
        goodscoredata = self.df.loc[self.df['总分'] >= goodtotal]
        chnav = goodscoredata['语文'].mean()
        mathav = goodscoredata['数学'].mean()
        engav = goodscoredata['英语'].mean()
        polav = goodscoredata['政治'].mean()
        hisav = goodscoredata['历史'].mean()
        geoav = goodscoredata['地理'].mean()
        physav = goodscoredata['物理'].mean()
        chemav = goodscoredata['化学'].mean()
        bioav = goodscoredata['生物'].mean()
        totalav = goodscoredata['总分'].mean()
        factor = goodtotal / totalav
        """计算各科有效分"""
        chn = round(chnav * factor)
        math = round(mathav * factor)
        eng = round(engav * factor)
        pol = round(polav * factor)
        his = round(hisav * factor)
        geo = round(geoav * factor)
        phys = round(physav * factor)
        chem = round(chemav * factor)
        bio = round(bioav * factor)
        if (chn + math + eng + pol + his + geo + phys + chem + bio) > goodtotal:
            math -= 1
        if (chn + math + eng + pol + his + geo + phys + chem + bio) < goodtotal:
            eng += 1

        return chn, math, eng, pol, his, geo, phys, chem, bio, goodtotal

    def goodscores(self, chn, math, eng, pol, his, geo, phys, chem, bio, total):
        """
        计算文科各科各班单有效和双有效人数
        """
        single_chn = self.df[self.df['语文'] >= chn].groupby(['班级'])['语文'].count()
        single_math = self.df[self.df['数学'] >= math].groupby(['班级'])['数学'].count()
        single_eng = self.df[self.df['英语'] >= eng].groupby(['班级'])['英语'].count()
        single_pol = self.df[self.df['政治'] >= pol].groupby(['班级'])['政治'].count()
        single_his = self.df[self.df['历史'] >= his].groupby(['班级'])['历史'].count()
        single_geo = self.df[self.df['地理'] >= geo].groupby(['班级'])['地理'].count()
        single_phys = self.df[self.df['物理'] >= phys].groupby(['班级'])['物理'].count()
        single_chem = self.df[self.df['化学'] >= chem].groupby(['班级'])['化学'].count()
        single_bio = self.df[self.df['生物'] >= bio].groupby(['班级'])['生物'].count()
        single_total = self.df[self.df['总分'] >= total].groupby(['班级'])['总分'].count()

        name_num = self.df.groupby(['班级'])['姓名'].count()
        name_num.name = '参考人数'

        df2 = self.df[self.df['总分'] >= total]
        double_chn = df2[df2['语文'] >= chn].groupby(['班级'])['语文'].count()
        double_math = df2[df2['数学'] >= math].groupby(['班级'])['数学'].count()
        double_eng = df2[df2['英语'] >= eng].groupby(['班级'])['英语'].count()
        double_pol = df2[df2['政治'] >= pol].groupby(['班级'])['政治'].count()
        double_his = df2[df2['历史'] >= his].groupby(['班级'])['历史'].count()
        double_geo = df2[df2['地理'] >= geo].groupby(['班级'])['地理'].count()
        double_phys = df2[df2['物理'] >= phys].groupby(['班级'])['物理'].count()
        double_chem = df2[df2['化学'] >= chem].groupby(['班级'])['化学'].count()
        double_bio = df2[df2['生物'] >= bio].groupby(['班级'])['生物'].count()
        double_total = df2[df2['总分'] >= total].groupby(['班级'])['总分'].count()

        goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '政治': pol, '历史': his,
                          '地理': geo,
                          '物理': phys, '化学': chem, '生物': bio, '总分': total}
        goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

        result_single = pd.concat([name_num, single_chn, single_math, single_eng,
                                   single_pol, single_his, single_geo,
                                   single_phys, single_chem, single_bio, single_total],
                                  axis=1)
        result_double = pd.concat(
            [name_num, double_chn, double_math, double_eng,
             double_pol, double_his, double_geo,
             double_phys, double_chem, double_bio, double_total], axis=1)

        result_single.loc['年级'] = [result_single['参考人数'].sum(),
                                     result_single['语文'].sum(),
                                     result_single['数学'].sum(),
                                     result_single['英语'].sum(),
                                     result_single['政治'].sum(),
                                     result_single['历史'].sum(),
                                     result_single['地理'].sum(),
                                     result_single['物理'].sum(),
                                     result_single['化学'].sum(),
                                     result_single['生物'].sum(),
                                     result_single['总分'].sum()
                                     ]
        result_single['上线率'] = result_single['总分'] / result_single['参考人数']
        result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
        result_double.loc['年级'] = [result_double['参考人数'].sum(),
                                     result_double['语文'].sum(),
                                     result_double['数学'].sum(),
                                     result_double['英语'].sum(),
                                     result_double['政治'].sum(),
                                     result_double['历史'].sum(),
                                     result_double['地理'].sum(),
                                     result_double['物理'].sum(),
                                     result_double['化学'].sum(),
                                     result_double['生物'].sum(),
                                     result_double['总分'].sum()]
        unmatched_dict = {'参考人数': name_num, '语文': single_total - double_chn,
                          '数学': single_total - double_math, '英语': single_total - double_eng,
                          '政治': single_total - double_pol, '历史': single_total - double_his,
                          '地理': single_total - double_geo, '物理': single_total - double_phys,
                          '化学': single_total - double_chem, '生物': single_total - double_bio,
                          '总分': single_total}
        unmatched_df = pd.DataFrame(unmatched_dict)
        unmatched_df.loc['年级'] = [unmatched_df['参考人数'].sum(),
                                    unmatched_df['语文'].sum(),
                                    unmatched_df['数学'].sum(),
                                    unmatched_df['英语'].sum(),
                                    unmatched_df['政治'].sum(),
                                    unmatched_df['历史'].sum(),
                                    unmatched_df['地理'].sum(),
                                    unmatched_df['物理'].sum(),
                                    unmatched_df['化学'].sum(),
                                    unmatched_df['生物'].sum(),
                                    unmatched_df['总分'].sum()]

        result_final = pd.concat([goodscore_df, result_single, result_double, unmatched_df], axis=0,
                                 keys=['有效分数', '单有效', '双有效', '错位数'])
        result_final.fillna(0, inplace=True)
        """分班计算错位生名单"""
        df_chn = df2.loc[:, ['班级', '姓名', '语文']].loc[df2['语文'] < chn].sort_values(by=['班级', '语文'],
                                                                                         ascending=[True,
                                                                                                    False]).reset_index(
            drop=True)
        df_math = df2.loc[:, ['班级', '姓名', '数学']].loc[df2['数学'] < math].sort_values(by=['班级', '数学'],
                                                                                           ascending=[True,
                                                                                                      False]).reset_index(
            drop=True)
        df_eng = df2.loc[:, ['班级', '姓名', '英语']].loc[df2['英语'] < eng].sort_values(by=['班级', '英语'],
                                                                                         ascending=[True,
                                                                                                    False]).reset_index(
            drop=True)
        df_pol = df2.loc[:, ['班级', '姓名', '政治']].loc[df2['政治'] < pol].sort_values(by=['班级', '政治'],
                                                                                         ascending=[True,
                                                                                                    False]).reset_index(
            drop=True)
        df_his = df2.loc[:, ['班级', '姓名', '历史']].loc[df2['历史'] < his].sort_values(by=['班级', '历史'],
                                                                                         ascending=[True,
                                                                                                    False]).reset_index(
            drop=True)
        df_geo = df2.loc[:, ['班级', '姓名', '地理']].loc[df2['地理'] < geo].sort_values(by=['班级', '地理'],
                                                                                         ascending=[True,
                                                                                                    False]).reset_index(
            drop=True)
        df_phys = df2.loc[:, ['班级', '姓名', '物理']].loc[df2['物理'] < phys].sort_values(by=['班级', '物理'],
                                                                                           ascending=[True,
                                                                                                      False]).reset_index(
            drop=True)
        df_chem = df2.loc[:, ['班级', '姓名', '化学']].loc[df2['化学'] < chem].sort_values(by=['班级', '化学'],
                                                                                           ascending=[True,
                                                                                                      False]).reset_index(
            drop=True)
        df_bio = df2.loc[:, ['班级', '姓名', '生物']].loc[df2['生物'] < bio].sort_values(by=['班级', '生物'],
                                                                                         ascending=[True,
                                                                                                    False]).reset_index(
            drop=True)

        unmatched_students = pd.concat([df_chn, df_math, df_eng, df_pol, df_his, df_geo, df_phys, df_chem, df_bio],
                                       axis=1)
        # 计算学科贡献率，命中率和等级评定
        shoot_dict = {'语文': result_double['语文'] / result_single['语文'],
                      '数学': result_double['数学'] / result_single['数学'],
                      '英语': result_double['英语'] / result_single['英语'],
                      '政治': result_double['政治'] / result_single['政治'],
                      '历史': result_double['历史'] / result_single['历史'],
                      '地理': result_double['地理'] / result_single['地理'],
                      '物理': result_double['物理'] / result_single['物理'],
                      '化学': result_double['化学'] / result_single['化学'],
                      '生物': result_double['生物'] / result_single['生物'],
                      '上线数80%': result_single['总分'] * 0.8}
        shoot_df = pd.DataFrame(shoot_dict)
        contribution_dict = {'语文': result_double['语文'] / result_double['总分'],
                             '数学': result_double['数学'] / result_double['总分'],
                             '英语': result_double['英语'] / result_double['总分'],
                             '政治': result_double['政治'] / result_double['总分'],
                             '历史': result_double['历史'] / result_double['总分'],
                             '地理': result_double['地理'] / result_double['总分'],
                             '物理': result_double['物理'] / result_double['总分'],
                             '化学': result_double['化学'] / result_double['总分'],
                             '生物': result_double['生物'] / result_double['总分'],
                             '上线数80%': result_double['总分'] * 0.8}
        contribution_df = pd.DataFrame(contribution_dict)
        result_single.fillna(0, inplace=True)
        result_double.fillna(0, inplace=True)
        shoot_df.fillna(0, inplace=True)
        contribution_df.fillna(0, inplace=True)
        grade = pd.DataFrame(columns=['语文', '数学', '英语', '政治', '历史', '地理', '物理', '化学', '生物'],
                             index=shoot_df.index)

        def grade_assess(subject):
            for i in shoot_df.index:
                if result_single['总分'].at[i] != 0:
                    if (result_single[subject].at[i]) >= (result_single['总分'].at[i]) * 0.8:
                        if (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'A'
                        elif (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] < 0.6):
                            grade[subject].at[i] = 'B'
                        elif (contribution_df[subject].at[i] < 0.7) & (shoot_df[subject].at[i] >= 0.6):
                            grade[subject].at[i] = 'C'
                        else:
                            grade[subject].at[i] = 'D'
                    else:
                        grade[subject].at[i] = 'E'
                else:
                    grade[subject].at[i] = 'F'

        grade_assess('语文')
        grade_assess('数学')
        grade_assess('英语')
        grade_assess('政治')
        grade_assess('历史')
        grade_assess('地理')
        grade_assess('物理')
        grade_assess('化学')
        grade_assess('生物')

        final_grade = pd.concat([result_single, result_double, contribution_df, shoot_df, grade],
                                keys=['单有效', '双有效', '贡献率', '命中率', '等级'])
        av_scores = self.get_av()
        writer = pd.ExcelWriter(r'D:\成绩统计结果\高一有效分统计表.xlsx')
        result_final.to_excel(writer, sheet_name='有效分统计', float_format='%.2f')
        final_grade.to_excel(writer, sheet_name='等级评估', float_format='%.2f')
        unmatched_students.to_excel(writer, sheet_name='错位生名单', index=False)
        av_scores.to_excel(writer, sheet_name='各科平均分', float_format='%.2f')
        writer.close()

    def concat_files(self):
        chn, math, eng, pol, his, geo, phys, chem, bio, total = self.get_good_scores(
            goodtotal=eval(input('请输入本次考试上线总分： ')))
        self.goodscores(chn, math, eng, pol, his, geo, phys, chem, bio, total)

    def processed_with_good_scores(self):
        self.goodscores(*SeniorOneExam.good_scores)

    def top_n_students(self):
        top_chn = self.get_top_n(self.df, '语文', n=SeniorOneExam.top_n)
        top_math = self.get_top_n(self.df, '数学', n=SeniorOneExam.top_n)
        top_eng = self.get_top_n(self.df, '英语', n=SeniorOneExam.top_n)
        top_pol = self.get_top_n(self.df, '政治', n=SeniorOneExam.top_n)
        top_his = self.get_top_n(self.df, '历史', n=SeniorOneExam.top_n)
        top_geo = self.get_top_n(self.df, '地理', n=SeniorOneExam.top_n)
        top_phy = self.get_top_n(self.df, '物理', n=SeniorOneExam.top_n)
        top_chem = self.get_top_n(self.df, '化学', n=SeniorOneExam.top_n)
        top_bio = self.get_top_n(self.df, '生物', n=SeniorOneExam.top_n)
        top_total = self.get_top_n(self.df, '总分', n=SeniorOneExam.top_n)

        top_subject_students = pd.concat([top_chn, top_math, top_eng, top_pol, top_his, top_geo,
                                          top_phy, top_chem, top_bio, top_total], axis=1)
        top_subject_students.index = [i + 1 for i in top_subject_students.index]
        with pd.ExcelWriter(r'D:\成绩统计结果\学科前N名.xlsx') as writer:
            top_subject_students.to_excel(writer, sheet_name='学科前N名', index_label='序号')

    def score_label(self):
        """
        计算各班学生成绩单
        :return:
        """
        self.df['排名'] = self.df['总分'].rank(method='min', ascending=False)
        exam_label = self.df.loc[:, ['班级', '姓名', '语文', '数学', '英语', '政治', '历史', '地理',
                                     '物理', '化学', '生物', '总分', '排名']]
        exam_label.sort_values(by=['班级', '总分'], inplace=True, ascending=[True, False], ignore_index=True)
        for i in exam_label.index:
            exam_label.loc[i + 0.5] = exam_label.columns
        exam_label.sort_index(inplace=True, ignore_index=True)

        with pd.ExcelWriter(r'D:\成绩统计结果\本次考试学生个人成绩单.xlsx') as writer:
            exam_label.to_excel(writer, sheet_name='文科成绩单', index=False)

    def individual_class_scores(self):
        self.df['排名'] = self.df['总分'].rank(method='min', ascending=False)
        # self.df['排名'] = self.df['排名'].apply(lambda x: format(int(x)))
        self.df.sort_values(by='总分', ascending=False, inplace=True)
        self.df.reset_index(inplace=True, drop=True)
        self.df['序号'] = [i + 1 for i in self.df.index]
        av_scores = self.get_av()
        class_numbers = self.df['班级'].unique()
        self.df = self.df.loc[:, ['序号', '班级', '姓名', '语文', '数学', '英语',
                                  '物理', '化学', '生物',
                                  '政治', '历史', '地理', '总分', '排名']]
        writer = pd.ExcelWriter(r'D:\成绩统计结果\本次高一年级考试成绩统计表.xlsx')
        self.df.to_excel(writer, sheet_name='年级总表', index=False)
        for i in class_numbers:
            class_number = self.df[self.df['班级'] == i].reset_index(drop=True)
            class_number['序号'] = [k + 1 for k in class_number.index]
            class_number = class_number.loc[:, ['序号', '班级', '姓名', '语文', '数学', '英语',
                                                '物理', '化学', '生物',
                                                '政治', '历史', '地理', '总分', '排名']]
            class_number.to_excel(writer, sheet_name=i, index=False)
        av_scores.to_excel(writer, sheet_name='平均分', float_format='%.2f')

        writer.close()

    def show_menu(self):
        while True:
            print(self.__str__())
            flag = eval(input(f'请按功能键选择：\n'
                              f'1、按已知有效分统计\n'
                              f'2、按划线总分统计\n'
                              f'3、生成考室安排表\n'
                              f'4、生成高一学生个人成绩单\n'
                              f'5、生成各班成绩表\n'
                              f'6、生成高一各科前N名学生\n'
                              f'7、按其它任意健退出\n请选择：'))
            if flag == 1:
                self.goodscores(*SeniorOneExam.good_scores)
                print(f'successfully done! thank you!')
                break

            elif flag == 5:
                self.individual_class_scores()
                print(f'successfully done! thank you!')
                break

            elif flag == 3:
                SeniorOneExam.room_num = eval(input('请输入每个考室人数：'))
                self.exam_room()
                print(f'successfully done! thank you!')
                break
            elif flag == 4:
                self.score_label()
                print(f'successfully done!thank you!')
                break
            elif flag == 2:
                self.concat_files()
                print(f'successfully done!thank you!')
                break
            elif flag == 6:
                SeniorOneExam.top_n = eval(input('请输入前N名：'))
                self.top_n_students()
                break
            else:
                break


class ExamInvigilators(object):
    """
    获取监考安排表
    """
    room_numbers = None
    teacher_numbers = None

    def __init__(self, filepath=r'D:\年级管理数据\高2021级\高二上\高二监考数据.xlsx'):
        self.filepath = filepath
        self.df = pd.read_excel(filepath, index_col='id', sheet_name='teacher_names')

    @staticmethod
    def exams_numbers():
        exam_num = 1
        while True:
            if (exam_num * ExamInvigilators.room_numbers) % ExamInvigilators.teacher_numbers == 0:
                break
            exam_num += 1
        return exam_num

    def exam_teachers(self):
        """
        计算分考室监考教师名单
        """
        exam_numbers = self.exams_numbers()
        df_teachers = self.df[self.df['leader'] != 'yes']
        df_teachers = df_teachers.loc[:, 'name']
        teacher_list = df_teachers.values
        lst = [i for i in range(len(teacher_list))]
        # random.seed(5)
        # random.shuffle(lst)
        # print(lst)
        teacher_list_random = []
        for i in lst:
            teacher_list_random.append(teacher_list[i])
        # random.seed(5)
        random.shuffle(teacher_list_random)
        # print(teacher_list)
        df_room = pd.DataFrame(columns=['Exam' + str(i + 1) for i in range(exam_numbers)],
                               index=['Room' + str(i + 1) for i in range(ExamInvigilators.room_numbers)])
        # df_room['Exam5'], df_room['Exam6'], df_room['Exam7'], df_room['Exam8'], df_room[
        #     'Exam9'] = None, None, None, None, None
        df_room.index.rename('room_numbers', inplace=True)
        df_room.reset_index(inplace=True)
        nums = 0
        try:
            for i in df_room.columns[1:]:
                for k in df_room.index:
                    # df_room[i].at[k] = df_teachers['name'].at[nums + 1]
                    df_room[i].at[k] = teacher_list_random[nums]
                    if nums < len(teacher_list) - 1:
                        nums += 1
                    else:
                        nums = 0
        except KeyError:
            df_room.fillna(np.NaN)
        else:
            print('succefully done')
        df_room.set_index('room_numbers', inplace=True)

        writer = pd.ExcelWriter(r'D:\成绩统计结果\监考安排信息表.xlsx')
        print(df_room)
        df_room.to_excel(writer, sheet_name='invigilation_table')
        teachers = df_room.values
        teacher_dict = {}
        for i in range(len(teachers)):
            for k in teachers[i]:
                teacher_dict[k] = teacher_dict.get(k, 0) + 1
        teacher_occupied = list(teacher_dict.items())
        teacher_occupied.sort(key=lambda x: x[1], reverse=True)
        invigilators = []
        invigilation_times = []
        for i in range(len(teacher_occupied)):
            teacher, times = teacher_occupied[i]
            invigilators.append(teacher)
            invigilation_times.append(times)
            print('{0:>6}老师：    监考{1:<}节'.format(teacher, times))
        print(f'共有{len(teacher_occupied)}个老师参与本次监考')
        invigilation_df = pd.DataFrame({'id': [i + 1 for i in range(len(invigilators))], 'teacher_names': invigilators,
                                        'invigilation_times': invigilation_times})
        invigilation_df.set_index('id', inplace=True)
        invigilation_df.loc['total'] = [invigilation_df['teacher_names'].count(),
                                        invigilation_df['invigilation_times'].sum()]
        invigilation_df.to_excel(writer, sheet_name='invigilation_staff')
        writer.close()


class JuniorExam(object):
    def __init__(self, file_path):
        self.file_path = file_path
        self.df = pd.read_excel(file_path, sheet_name='总表')

    def junior_scores(self):
        """
        计算初中考试合格人数及合格率
        :return: 生成一个excel文件
        """
        if '化学' in self.df.columns:
            qualification_df = self.df.loc[:,
                               ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '物理A卷', '化学', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()
            single_phys = qualification_df[qualification_df['物理A卷'] >= 60].groupby(['班级'])['物理A卷'].count()
            single_chem = qualification_df[qualification_df['化学'] >= 60].groupby(['班级'])['化学'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng,
                                        single_phys, single_chem],
                                       axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          qualified_data['物理A卷'].sum(),
                                          qualified_data['化学'].sum()]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['物理合格率'] = qualified_data['物理A卷'] / qualified_data['参考人数']
            qualified_data['物理合格率'] = qualified_data['物理合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['化学合格率'] = qualified_data['化学'] / qualified_data['参考人数']
            qualified_data['化学合格率'] = qualified_data['化学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率',
                           '物理A卷', '物理合格率', '化学', '化学合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data
        elif '物理A卷' not in self.df.columns:
            qualification_df = self.df.loc[:, ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng], axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          ]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data
        else:
            qualification_df = self.df.loc[:,
                               ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '物理A卷', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()
            single_phys = qualification_df[qualification_df['物理A卷'] >= 60].groupby(['班级'])['物理A卷'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng,
                                        single_phys], axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          qualified_data['物理A卷'].sum()]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['物理合格率'] = qualified_data['物理A卷'] / qualified_data['参考人数']
            qualified_data['物理合格率'] = qualified_data['物理合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率',
                           '物理A卷', '物理合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data

    def get_av(self):
        # 计算平均分
        if '化学' in self.df.columns:

            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷', '物理B卷', '物理合卷',
                                                  '化学', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷', '物理B卷', '物理合卷',
                                  '化学', '总分']].apply(np.nanmean, axis=0).round(2)
            av_general.name = '年级平均分'
            av_scores = av_class.append(av_general)
            return av_scores
        elif '物理合卷' not in self.df.columns:
            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '总分']].apply(np.nanmean, axis=0).round(2)
            av_general.name = '年级平均分'
            av_scores = av_class.append(av_general)
            return av_scores
        else:
            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷',
                                                  '物理A卷', '物理B卷', '物理合卷', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷',
                                  '物理B卷', '物理合卷', '总分']].apply(np.nanmean, axis=0).round(2)
            av_general.name = '年级平均分'
            av_scores = av_class.append(av_general)
            return av_scores

    def get_goodscores(self, goodtotal):
        """
        计算各科有效分
        goodtotal:划线总分，高线，中线，低线
        """
        if '化学' in self.df.columns:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '化学', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            phyav = goodscoredata['物理合卷'].mean()
            chemav = goodscoredata['化学'].mean()
            totalav = goodscoredata['总分'].mean()
            factor = goodtotal / totalav
            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)
            phy = round(phyav * factor)
            chem = round(chemav * factor)
            if (chn + math + eng + phy + chem) > goodtotal:
                math -= 1
            if (chn + math + eng + phy + chem) < goodtotal:
                chn += 1
            return chn, math, eng, phy, chem, goodtotal

        elif '物理合卷' not in self.df.columns:
            good_score_df = self.df.loc[:, ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            totalav = goodscoredata['总分'].mean()

            factor = goodtotal / totalav

            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)

            if (chn + math + eng) > goodtotal:
                math -= 1
            if (chn + math + eng) < goodtotal:
                chn += 1
            return chn, math, eng, goodtotal

        else:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']
            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            phyav = goodscoredata['物理合卷'].mean()
            totalav = goodscoredata['总分'].mean()
            factor = goodtotal / totalav
            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)
            phy = round(phyav * factor)
            if (chn + math + eng + phy) > goodtotal:
                math -= 1
            if (chn + math + eng + phy) < goodtotal:
                chn += 1
            return chn, math, eng, phy, goodtotal

    def goodscore_process(self, goodtotal):
        """
        计算各科各班单有效和双有效人数
        """
        # 计算各班有效学生人数

        if '化学' in self.df.columns:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '化学', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            chn, math, eng, phy, chem, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_phy, double_phy = self.get_single_double_score(good_score_df, '物理', phy, total)
            single_chem, double_chem = self.get_single_double_score(good_score_df, '化学', chem, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng,
                              '物理': phy, '化学': chem, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng,
                                       single_phy, single_chem, single_total],
                                      axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng,
                 double_phy, double_chem, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['物理'].sum(),
                                         result_single['化学'].sum(),
                                         result_single['总分'].sum()
                                         ]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['物理'].sum(),
                                         result_double['化学'].sum(),
                                         result_double['总分'].sum()
                                         ]
            final_result = pd.concat([goodscore_df, result_single, result_double])

            return final_result
        elif '物理合卷' not in self.df.columns:
            good_score_df = self.df.loc[:, ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            chn, math, eng, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng, single_total], axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['总分'].sum()]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['总分'].sum()]
            final_result = pd.concat([goodscore_df, result_single, result_double])

            return final_result

        else:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']
            chn, math, eng, phy, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_phy, double_phy = self.get_single_double_score(good_score_df, '物理', phy, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng,
                              '物理': phy, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng,
                                       single_phy, single_total],
                                      axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng,
                 double_phy, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['物理'].sum(),
                                         result_single['总分'].sum()
                                         ]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['物理'].sum(),
                                         result_double['总分'].sum()
                                         ]
            final_result = pd.concat([goodscore_df, result_single, result_double])

            return final_result

    def concat_files(self):
        total = eval(input(f'请输入上线总分：'))
        good_scores = self.goodscore_process(total)
        junior_qualification = self.junior_scores()
        junior_av = self.get_av()
        with pd.ExcelWriter(r'D:\成绩统计结果\初中成绩统计分析.xlsx') as writer:
            good_scores.to_excel(writer, sheet_name='有效分')
            junior_qualification.to_excel(writer, sheet_name='合格率', index=False)
            junior_av.to_excel(writer, sheet_name='平均分')
        print(f'successfully done')

    @staticmethod
    def get_single_double_score(data, subject, subject_score, total_score):
        single = data[data[subject] >= subject_score].groupby(['班级'])[subject].count()
        data_double = data[data['总分'] >= total_score]
        double = data_double[data_double[subject] >= subject_score].groupby(['班级'])[subject].count()
        return single, double


if __name__ == '__main__':

    # 输入要处理的文件路径及文件名
    file = r'D:\年级管理数据\高2021级\高一上\高一上期末考试\高2021级高一（上）期末考试总成绩表.xlsx'
    # 输入学科前N名：
    SeniorOneExam.top_n = 3
    # 输入每间考室人数：
    SeniorOneExam.room_num = 35
    # 输入各科有效分数及总分
    SeniorOneExam.good_scores = [92, 70, 77, 54, 41, 39, 24, 21, 27, 445]
    # 实例化对象
    exam = SeniorOneExam(filepath=file)
    exam_invigilators = ExamInvigilators()
    ExamInvigilators.room_numbers = 9
    ExamInvigilators.teacher_numbers = 21
    junior_exam = JuniorExam(file)


    @exam.make_directory
    @exam.title_lines
    @exam.use_time
    def scores_assistant():
        while True:

            flag = eval(input(f'请按功能键选择：\n'
                              f'1、用已知有效分计算\n'
                              f'2、生成各班成绩表\n'
                              f'3、生成考室安排表\n'
                              f'4、生成学生个人成绩单\n'
                              f'5、生成成绩分析表\n'
                              f'6、生成监考信息表\n'
                              f'7、生成各科前N名学生\n'
                              f'8、生成初中成绩分析\n'
                              f'9、按其它任意健退出\n请选择：'))
            if flag == 1:
                exam.goodscores(*SeniorOneExam.good_scores)
                print(f'successfully done! thank you!')
                break

            elif flag == 2:
                exam.individual_class_scores()
                print(f'successfully done! thank you!')
                break

            elif flag == 3:
                exam.exam_room()
                print(f'successfully done! thank you!')
                break
            elif flag == 4:
                exam.score_label()
                print(f'successfully done!thank you!')
                break
            elif flag == 5:
                exam.concat_files()
                print(f'successfully done!thank you!')
                break
            elif flag == 6:
                exam_invigilators.exam_teachers()
                break
            elif flag == 7:
                exam.top_n_students()
                break
            elif flag == 8:
                junior_exam.concat_files()
                break
            else:
                print('程序已退出，谢谢使用！')
                break


    # 运行程序：
    scores_assistant()
